Spreadsheet applications (e.g., MICROSOFT EXCEL, GOOGLE DOCS, OPEN OFFICE) are primarily designed for performing computations on numeric values and presenting numeric data. Typically, a spreadsheet application displays multiple cells that together make up a grid consisting of rows and columns. Each spreadsheet cell may be associated with a variety of cell properties. For instance, a cell may contain a simple data value including alphanumeric text or a numeric value. Similarly, a cell may contain a formula for calculating a numeric value. When a spreadsheet cell contains a formula, the formula defines how the contents of that cell are to be calculated from the contents of any other cell (or combination of cells) each time any cell is updated. Finally, a cell may be associated with one or more cell properties defining how the data (directly defined, or derived by formula) are to be formatted when displayed.
The data displayed within the cells of a spreadsheet are typically stored within one or more data structures (e.g., cell objects) that are maintained and managed by the spreadsheet application, or in some cases, a third-party application. The spreadsheet component that reads the data from the spreadsheet application's native cell storage structure(s) and generates a visual representation of the data is referred to as a render engine. It is the render engine, for example, that processes the various cell properties (e.g., data, formulas and formatting directives) associated with each spreadsheet cell to generate a visual representation of the data associated with each cell. When a user changes the data value of a cell, the render engine updates the visual representation of that cell, as well as any other cells with formulas that are dependent upon the updated cell.
Recently, software application vendors—particularly vendors with software applications used for managing, presenting and reporting business information, such as financial data—have been increasingly providing a mechanism for presenting application data within a cell region of a spreadsheet in a spreadsheet application. To distinguish between cell regions that display data maintained and managed by a third-party application and cell regions that display data maintained and managed by the spreadsheet application, those cell regions displaying data maintained and managed by a third-party application are referred to herein as application-defined cell regions, while those cell regions displaying data that are maintained and managed by the spreadsheet application are referred to herein as user-defined cell regions.
Unfortunately, the native render engine of many spreadsheet applications is not designed to adequately handle some of the advanced data processing and presentation techniques that are made available by third-party applications. Specifically, in a situation where a third-party application is configured to present data via the interface of a spreadsheet application, when the processing of certain commands results in the insertion of a new cell region or the appendage of new cells to an existing cell region, the native render engine of the spreadsheet application may not generate a proper visual representation of the affected cells. In some cases, the insertion of a new cell region, or the appendage of new cells to an existing cell region, results in the overwriting of the cells of another existing cell region. This may occur, for example, as a result of processing a data insertion command, a data refresh command, or a command associated with an advanced data presentation method. In any case, oftentimes the result is that cell properties of an existing cell are overwritten and lost. This may have a domino effect as it impacts not only those cells which are directly overwritten, but also any cells that contain formulas that reference a cell that is overwritten. The following examples provide a more precise description of the nature of the problem.
In FIG. 1, two tables (i.e., Table A and Table B) are shown prior to, and after, a data refresh command is processed by the native render engine of a spreadsheet application. In this example, Table A contains and displays data that are managed and maintained by a third-party application. As such, Table A is an application-defined cell region and is subject to being asynchronously and dynamically refreshed by the third-party software application. When the data refresh command is processed, the data set increases and additional rows of data are appended to the bottom of Table A. For example, the rows in Table A associated with ID's 8, 9 and 10 are appended to the bottom of Table A as a result of processing the refresh command. Consequently, the first three columns of the four columns in Table B are shifted downward by three cells to accommodate the addition of the three rows to Table A. As illustrated in FIG. 1, as a result of processing the data refresh command, the rows of Table B are incorrectly displayed. In this example, only the layout of the cell region comprising Table B appears to be broken. However, it should be readily apparent that the problem is compounded when formulas are involved. For instance, if any data displayed in Table B are derived from a formula, the repositioning of the cells of Table B may break the formula, resulting in incorrect data being displayed. Similarly, when the cells of another cell region (e.g., other than Table B) derive their values from the data in the cells of Table B, the repositioning of cells in Table B will break the formulas associated with those other cells and again cause incorrect data to be displayed.
To address the problem illustrated in FIG. 1, in some scenarios, the user of the spreadsheet application may be left with no choice but to resolve the layout issue manually, for example, by anticipating the potential size of Table A, and ensuring that the destination region is large enough to display any future cells appended to Table A. However, when Table A is an application-defined cell region, such that the underlying data is maintained and managed by a third-party software application, it may be difficult, if not impossible, for the user to anticipate future changes to the data and the ultimate size of the data set. Accordingly, the data presented in Table A may be automatically and dynamically refreshed, thereby increasing or decreasing the size of Table A.
FIG. 2 illustrates another example of a problem that may exist with a spreadsheet application's native render engine when used in conjunction with third-party data presented in an application-defined cell region of the spreadsheet. In FIG. 2, Table A represents an application-defined cell region, which supports an advanced data presentation method or technique sometimes referred to as “drilling down.” Specifically, some of the cells within Table A can be selected, or “clicked” on (e.g., with a cursor control device, such as a mouse button), causing the table to be expanded (or collapsed) to show (or hide) additional data embedded within the cell region. As illustrated in FIG. 2, after a user has selected the cell in Table A with value “+Drink”, Table A is expanded to present three additional columns. However, the expansion of Table A causes data in the cells of Table B to be overwritten with data from Table A. Here again, the cell properties, including formulas, of Table B may be broken.